﻿Imports System.Data.SqlClient
Public Class FrmDMHangHoa
    Dim themmoi As Boolean = True
    Public thanhcong As Boolean = False
    Private Sub FrmDMHangHoa_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cbxDVT.DataSource = TruyVanDanhSach("select * from DonVi")
        cbxDVT.DisplayMember = "TenDV"
        cbxDVT.ValueMember = "MaDV"
        cbxDVT.SelectedIndex = -1
        UpdateList()
    End Sub

    Public Function TruyVanDanhSach(ByVal strSql As String) As DataTable
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.Fill(myTable)
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return myTable
    End Function

    Private Sub UpdateList(Optional ByVal keyword As String = "")
        Dim strSql As String = "select ROW_NUMBER() OVER (ORDER BY hh.MaHH) as STT, hh.MaHH as [Mã hàng hóa], hh.TenHH as [Tên hàng hóa], dv.TenDV as [Đơn vị tính], hh.DienGiai as [Diễn giải], hh.MaDV from HangHoa hh left join DonVi dv on dv.MaDV = hh.MaDV "
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            keyword = keyword.Trim
            If keyword <> "" Then
                strSql &= " Where hh.MaHH like N'%" & keyword & "%'"
            End If
            strSql &= " order by STT"
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.Fill(myTable)
            dgvList.DataSource = myTable
            dgvList.Columns.Item(0).Width = 50
            dgvList.Columns.Item(1).Width = 100
            dgvList.Columns.Item(2).Width = 300
            dgvList.Columns.Item(3).Width = 100
            dgvList.Columns.Item(4).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
            dgvList.Columns.Item(5).Visible = False
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
    End Sub

    Private Function DeleteData(ByVal MaHH As String) As Boolean
        Dim strSql As String = "Delete from HangHoa where MaHH = @MaHH"
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myComm As New SqlCommand
        Dim result As Boolean = True
        Try
            myConn.Open()
            myComm.Connection = myConn
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@MaHH", MaHH)
            myComm.ExecuteNonQuery()
        Catch ex As Exception
            result = False
        End Try
        myConn.Close()
        myConn.Dispose()
        Return result
    End Function

    Private Sub dgvList_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvList.KeyDown
        If e.KeyCode = Keys.Delete Then
            If Me.dgvList.SelectedRows.Count > 0 Then
                If MsgBox("Xóa Hàng hóa đã chọn?", MsgBoxStyle.OkCancel, "Xác nhận") = MsgBoxResult.Ok Then
                    If DeleteData(Me.dgvList.SelectedRows.Item(0).Cells(1).Value) Then
                        UpdateList()
                    Else
                        MsgBox("Lỗi xóa dữ liệu!")
                    End If
                End If
            End If
        End If
    End Sub

    Private Sub btnTim_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTim.Click
        UpdateList(Me.txtKey.Text)
    End Sub

    Private Sub dgvList_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgvList.SelectionChanged
        If dgvList.SelectedRows.Count > 0 Then
            txtMaHH.Text = dgvList.SelectedRows(0).Cells(1).Value.ToString()
            txtTenHH.Text = dgvList.SelectedRows(0).Cells(2).Value.ToString()
            If dgvList.SelectedRows(0).Cells(5).Value.ToString() <> "" Then
                cbxDVT.SelectedValue = dgvList.SelectedRows(0).Cells(5).Value.ToString()
            Else
                cbxDVT.SelectedIndex = -1
            End If
            txtDienGiai.Text = dgvList.SelectedRows(0).Cells(4).Value.ToString()
        End If
    End Sub

    Private Sub btnThem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThem.Click
        txtMaHH.Text = ""
        txtTenHH.Text = ""
        txtDienGiai.Text = ""
        txtMaHH.ReadOnly = False
        txtTenHH.ReadOnly = False
        txtDienGiai.ReadOnly = False
        cbxDVT.Enabled = True
        btnThemDonVi.Enabled = True
        btnLuu.Enabled = True
        btnSua.Enabled = False
        themmoi = True
    End Sub

    Private Sub btnXoa_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoa.Click
        If Me.dgvList.SelectedRows.Count > 0 Then
            If MsgBox("Xóa khách hàng đã chọn?", MsgBoxStyle.OkCancel, "Xác nhận") = MsgBoxResult.Ok Then
                If DeleteData(Me.dgvList.SelectedRows.Item(0).Cells(1).Value) Then
                    UpdateList()
                Else
                    MsgBox("Lỗi xóa dữ liệu!")
                End If
            End If
        Else
            MsgBox("Vui lòng chọn dòng cần Xóa!")
        End If
    End Sub

    Private Sub btnSua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSua.Click
        txtTenHH.ReadOnly = False
        txtDienGiai.ReadOnly = False
        cbxDVT.Enabled = True
        btnThemDonVi.Enabled = True
        btnLuu.Enabled = True
        themmoi = False
    End Sub

    Private Sub btnDong_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDong.Click
        Me.Close()
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        diChuyenChon(1)
    End Sub

    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        diChuyenChon(2)
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        diChuyenChon(3)
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        diChuyenChon(4)
    End Sub

    Private Sub diChuyenChon(ByVal kind As Integer)
        If dgvList.Rows.Count > 0 Then
            Dim curIndex As Integer = 0
            If dgvList.CurrentRow IsNot Nothing Then
                curIndex = dgvList.CurrentRow.Index
            End If
            Select Case kind
                Case 1
                    curIndex = 0
                Case 2
                    If curIndex > 0 Then
                        curIndex = curIndex - 1
                    End If
                Case 3
                    If curIndex <> dgvList.Rows.Count - 1 Then
                        curIndex = curIndex + 1
                    End If
                Case 4
                    curIndex = dgvList.Rows.Count - 1
            End Select
            dgvList.CurrentCell = dgvList.Item(0, curIndex)
        End If
    End Sub

    Private Sub btnHuyBo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHuyBo.Click
        HuyBo()
        If dgvList.SelectedRows.Count > 0 Then
            txtMaHH.Text = dgvList.SelectedRows(0).Cells(1).Value.ToString()
            txtTenHH.Text = dgvList.SelectedRows(0).Cells(2).Value.ToString()
            If dgvList.SelectedRows(0).Cells(5).Value.ToString() <> "" Then
                cbxDVT.SelectedValue = dgvList.SelectedRows(0).Cells(5).Value.ToString()
            Else
                cbxDVT.SelectedIndex = -1
            End If
            txtDienGiai.Text = dgvList.SelectedRows(0).Cells(4).Value.ToString()
        End If
    End Sub

    Private Sub HuyBo()
        txtMaHH.ReadOnly = True
        txtTenHH.ReadOnly = True
        txtDienGiai.ReadOnly = True
        cbxDVT.Enabled = False
        btnThemDonVi.Enabled = False
        btnLuu.Enabled = False
        btnSua.Enabled = True
    End Sub

    Private Sub btnLuu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLuu.Click
        If validateInput() Then
            If CapNhat() Then
                thanhcong = True
                MsgBox("Lưu thành công")
                UpdateList()
                If themmoi Then
                    txtMaHH.Text = ""
                    txtTenHH.Text = ""
                    txtDienGiai.Text = ""
                Else
                    HuyBo()
                End If
            Else
                MsgBox("Lưu thất bại")
            End If
        End If
    End Sub

    Private Function CapNhat() As Boolean
        Dim strSql As String = ""
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myComm As New SqlCommand
        Dim result As Boolean = True
        Try
            myConn.Open()
            myComm.Connection = myConn
            If themmoi Then
                strSql = "Insert into HangHoa(MaHH, TenHH, MaDV, DienGiai) values (@MaHH, @TenHH, @MaDV, @DienGiai)"
            Else
                strSql = "Update HangHoa set TenHH = @TenHH, MaDV = @MaDV, DienGiai = @DienGiai where MaHH = @MaHH"
            End If
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@MaHH", txtMaHH.Text.Trim())
            myComm.Parameters.AddWithValue("@TenHH", txtTenHH.Text.Trim())
            myComm.Parameters.AddWithValue("@MaDV", cbxDVT.SelectedValue)
            myComm.Parameters.AddWithValue("@DienGiai", txtDienGiai.Text)
            myComm.ExecuteNonQuery()
        Catch ex As Exception
            result = False
        End Try
        myConn.Close()
        myConn.Dispose()
        Return result
    End Function

    Private Function checkExist(ByVal MaHH As String) As Boolean
        Dim strSql As String = "Select count(*) from HangHoa where MaHH = @MaHH"
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myComm As New SqlCommand
        Dim result As Boolean = False
        Try
            myConn.Open()
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@MaHH", MaHH)
            myComm.Connection = myConn
            If myComm.ExecuteScalar() > 0 Then
                result = True
            End If
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return result
    End Function

    Private Function validateInput() As Boolean
        If Me.txtMaHH.Text.Trim = "" Then
            MsgBox("Mã hàng hóa không thể rỗng.", MsgBoxStyle.Information)
            txtMaHH.Focus()
            Return False
        Else
            If checkExist(Me.txtMaHH.Text.Trim) Then
                If themmoi Then
                    MsgBox("Mã hàng hóa đã tồn tại", MsgBoxStyle.Information)
                    txtMaHH.Focus()
                    Return False
                End If
            ElseIf Not themmoi Then
                MsgBox("Mã hàng hóa chưa tồn tại", MsgBoxStyle.Information)
                txtMaHH.Focus()
                Return False
            End If
        End If

        If Me.txtTenHH.Text.Trim = "" Then
            MsgBox("Tên hàng hóa không thể rỗng.", MsgBoxStyle.Information)
            txtTenHH.Focus()
            Return False
        End If

        Return True
    End Function

    Private Sub btnThemDonVi_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThemDonVi.Click
        Dim frm As New frmDMDonVi()
        frm.StartPosition = FormStartPosition.CenterScreen
        frm.ShowDialog()
        If frm.thanhcong Then
            cbxDVT.DataSource = TruyVanDanhSach("select * from DonVi")
            cbxDVT.DisplayMember = "TenDV"
            cbxDVT.ValueMember = "MaDV"
            cbxDVT.SelectedIndex = -1
        End If
    End Sub
End Class